#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date    : 2022/8/24 10:25
# @Author  : 招财大龙猫
# @File    : devweb_exceltodb.py
# @Description : dev_web 清洗
import pandas as pd
import sqlalchemy
from sqlalchemy.orm import sessionmaker

engine = sqlalchemy.create_engine('postgresql+psycopg2://postgres:sams111@127.0.0.1/sams', encoding='utf-8',
                                  echo=False)
Session = sessionmaker(bind=engine)
select_dev_info_sql = "select * from dev_info where dev_info.remarks = '1'"

if __name__ == '__main__':
    sess = Session()
    src_data = pd.read_excel(r'xg_data.xls')
    src_data = src_data[['IP', '客户提供']]
    dev_info_data = pd.read_sql(sql=select_dev_info_sql, con=engine)
    src_data_ls = src_data.to_dict('records')
    res = []
    print('正在处理数据。。。。。')
    for i in src_data_ls:
        ls_ip = i.get('IP')
        ls_type = i.get('客户提供')
        query_df = dev_info_data.query(f"ip=='{ls_ip}'")
        if len(query_df):
            query_df = query_df.drop(
                columns=['web_status', 'etl_status', 'is_flow', 'flowsum', 'ct_status', 'probe_name'])
            query_dict = query_df.to_dict('records')[0]
            query_dict['type'] = ls_type
            result = pd.read_sql_query(f"select ip from dev_info_web where ip = '{ls_ip}'", engine)
            if len(result):
                try:
                    sess.execute(f"update dev_info_web set type = '{ls_type}' where ip = '{ls_ip}'")
                    sess.commit()
                except Exception as exp:
                    print('更新失败', exp)
            else:
                res.append(query_dict)

    if res:
        df = pd.DataFrame(res)
        df.to_sql('dev_info_web', engine, index=False, if_exists='append')

    sess.execute(f"UPDATE dev_info_release SET up_status = 1")
    sess.commit()
    sess.close()
    print('更新成功')
